Re: Slow queries in PL/PGSQL function

Thanks for the help with this.  I followed Richard's advice and changed the
function to EXECUTE the query so it would be planned with the actual values at
the time the query would be run, and it worked as expected.

>Specifically, the only part of that that looks indexable is the
>dts_last_modified constraint.  If it's always "dts_last_modified >
>some-time-in-the-recent-past" then the range of values scanned is going
>to be small enough to make an indexscan worthwhile.  But if what the
>planner sees is "dts_last_modified > variable" then it isn't gonna risk
>an indexscan, because in the general case that could mean scanning a
>large part of the table, and the indexscan would be tremendously slow.

Actually, dts_last_modified was not indexed.  A sequential scan on
dts_last_modified in x_rbl_ips was very fast even with 250K records, around .5
second for the entire query.  The only reason I could think that the query would
take up to several minutes when run from the function is that it was joining the
entire 250K rows of x_rbl_ips with the 7M rows of filter_ips before filtering on
x_rbl_ips.dts_last_modified.

>What you can do to work around this (I'm assuming dts_last_modified
>never contains values in the future) is add a dummy constraint:
>
>   WHERE x_rbl_ips.dts_last_modified > variable
>     AND x_rbl_ips.dts_last_modified <= now()
>     AND other-stuff
>
>Now what the planner sees is dts_last_modified being constrained to a
>range, rather than an open-ended interval.  It still has to guess about
>how much of the index will be scanned, but its guess in this scenario
>is much smaller and it should usually pick the indexscan instead.

Running the query with FOR loop_rec IN EXECUTE turned out to be so easy and fast
that I didn't try this.  However, as I mentioned, x_rbl_ips.dts_last_modified
wasn't indexed in the first place so I don't know if it would have helped.

>BTW, in recent releases you can investigate planner choices involving
>queries with variables by using PREPARE and EXPLAIN EXECUTE.  For

This will be very useful for future testing.  I spend quite a bit of time
looking through the docs, and hadn't found this yet.

Thanks for the help.

--
Jim Crate
Deep Sky Technologies, Inc.